package com.zph.dao;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.csvreader.CsvReader;
import com.zph.entity.Constants;
import com.zph.entity.ContactList;

public class ImportList {

	public static HashMap<String, Integer> m_UserDic = new HashMap<String, Integer>(15000, 0.75f);

	static {
		init("1");
	}

	/**
	 * 初始化用户自定义词典
	 * C:\\Users\\Shu\\Desktop\\eclipse-jee-mars-1-win32-x86_64\\eclipse\\寻访记录\\
	 * ImportList.txt
	 */
	public static int init(String fileName) {
		InputStream in =null;
		String[] str = null;
		if (fileName != null && !fileName.equals("")) {
			try {  
				   in = Constants.class.getResourceAsStream(Constants.ImportList);
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			return 1;
		}
		try {
			String tempStr = "";
			BufferedReader read = new BufferedReader(new InputStreamReader(in, "utf-8"));
			while ((tempStr = read.readLine()) != null) {
				str = tempStr.split(",|/");
				m_UserDic.put(str[0], Integer.parseInt(str[1]));
			}
			str = null;
			tempStr = null;
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (in != null) {
					in.close();
					in = null;
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return 0;
	}

	/**
	 * 识别姓名
	 * 
	 */
	public static String detectName(String content) {

		/* 空格替换分隔符 */
		content = transferCharacter(content);
		/* 基本信息字段 */
		String[] contents = content.split("\\s+");
		  
		/* 百家姓字典数组 */
		String[] str = null;
		
		InputStream in = null ; 
		try {
			
		    in = Constants.class.getResourceAsStream(Constants.baijiaxing);
			String tempStr = "";
			BufferedReader read = new BufferedReader(new InputStreamReader(in, "utf-8"));
			
 			
			int count = 0;
			while ((tempStr = read.readLine()) != null) {
				str = tempStr.split("\\s+");
				// m_UserDic.put(str[0], Integer.parseInt(str[1]));
			}
			 
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				
				if (in != null) {
					in.close();
					in = null;
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

		String strOne = null;
		for (String contentOne : contents) { 
			for (int i = 0; i < str.length; i++) {
				if (contentOne.length() >= 2
						&& (contentOne.substring(0, 1).contains(str[i]) || contentOne.substring(0, 2).contains(str[i]))
						&& contentOne.length() <= 4) {
					 	
					strOne = contentOne;
					return strOne;
				}
			}
		}
		return strOne;
	}

	public static String transferCharacter(String content) {

		content = content.replace("-", " ");
		content = content.replace(":", " ");
		// content = content.replace( "."," ");
		content = content.replace(",", " ");
		content = content.replace("/", " ");
		content = content.replace("%", " ");
		content = content.replace("#", " ");
		content = content.replace("!", " ");
		// content = content.replace("＠", "@");
		content = content.replace("&", " ");
		content = content.replace("(", " ");
		content = content.replace(")", " ");
		content = content.replace("<", " ");
		content = content.replace(">", " ");
		content = content.replace("\"", " ");
		// content = content.replace("＇", "'");
		content = content.replace("?", " ");
		content = content.replace("[", " ");
		content = content.replace("]", " ");
		content = content.replace("{", " ");
		content = content.replace("}", " ");
		content = content.replace("\\\\", " ");
		content = content.replace("|", " ");
		content = content.replace("+", " ");
		content = content.replace("=", " ");
		content = content.replace("_", " ");
		content = content.replace("^", " ");
		content = content.replace("$", " ");
		content = content.replace("~", " ");
		content = content.replace("`", " ");

		return content;
	}

	private static final DataFormatter FORMATTER = new DataFormatter();

	/**
	 * 获取单元格内容
	 * 
	 * @param cell
	 *            单元格对象
	 * @return 转化为字符串的单元格内容
	 */
	private static String getCellContent(Cell cell) {
		return FORMATTER.formatCellValue(cell);
	}

	/* 读取Excel文件内容（单个sheet） */
	private static ContactList getExcelValueBehind(List<ContactList> list, String filePath, int sheetIndex,
			String userFileName, Workbook book, int userID, int companyID, int id, IDatabaseDao dao) {
		int currentid = 0;
		String value = "";
		ContactList contactList = new ContactList();
		try {
			Sheet sheet = book.getSheetAt(sheetIndex);
			// 获取到Excel文件中的所有行数
			int rows = sheet.getLastRowNum();
			// 遍历行
			String[] strArray = new String[2000];
			int numberofdatastructure = 1;
			for (int i = 0; i <= rows; i++) {
				int compactcolomn = 0;
				// 读取左上端单元格
				Row row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					// 获取到Excel文件中的所有的列
					int cells = row.getLastCellNum();
					String name = null;
					String number = null;
					String position = null;
					String department = null;
					String reportPerson = null;
					String nickeName = null;
					String email = null;
					String internalLine = null;
					String cellPhone = null;
					String team = null;
					String other = null;
					String customerID = null;
					String candidateID = null;
					// 遍历列
					for (int j = 0; j <= cells; j++) {
						// 获取到列的值
						Cell cell = row.getCell(j);
						if (cell != null) {
							if (strArray[j] == null) {
								// 解析非合并单元格
								String str = getCellContent(cell).substring(0).trim();
								str = str.replaceAll("\r", "");
								str = str.replaceAll("\n", "");
								str = str.replaceAll("\t", "");
								str = str.replaceAll("\\/", "");
								str = str.replaceAll(" ", "");
								if (str != "" && str != null) {
									int type = 0;
									if (m_UserDic.containsKey(str)) {// 识别到表头
										type = m_UserDic.get(str);
										switch (type) {
										case 1:
											strArray[j] = "姓名";
											break;
										case 2:
											strArray[j] = "工号";
											break;
										case 3:
											strArray[j] = "职位";
											break;
										case 4:
											strArray[j] = "部门";
											break;
										case 5:
											strArray[j] = "汇报对象";
											break;
										case 6:
											strArray[j] = "HR花名";
											break;
										case 7:
											strArray[j] = "邮箱";
											break;
										case 8:
											strArray[j] = "内线电话";
											break;
										case 9:
											strArray[j] = "手机";
											break;
										case 10:
											strArray[j] = "团队";
											break;
										default:
											strArray[j] = "其它";
											break;
										}// switch
									} // if
									else {
										String strOne = detectName(str);
										if (strOne != null && strOne != "") {
											strArray[j] = "姓名";
											name = str;
										}
										if (strArray[j] == null) {
											Pattern pPhone = Pattern
													.compile("13\\d{9}|15\\d{9}|18\\d{9}|14\\d{9}|17\\d{9}");
											Matcher matcher = pPhone.matcher(str);
											while (matcher.find()) {
												strArray[j] = "手机";
												break;
											}
										}
										if (strArray[j] == null) {
											Pattern pEmail = Pattern.compile(
													"([0-9A-Za-z\\-_\\.]+)@([0-9a-z]+\\.[a-z]{2,3}(\\.[a-z]{2})?)");
											Matcher matcher = pEmail.matcher(str);
											while (matcher.find()) {
												strArray[j] = "邮箱";
												break;
											}
										}
										if (strArray[j] == null) {
											Pattern pEmail = Pattern.compile(
													"\\d{3,4}-\\d{4}-\\d{4}|\\d{3,4}-\\d{6,8}|\\d{3,4}-\\d{4}|\\d{7,8}|\\d{11,12}");
											Matcher matcher = pEmail.matcher(str);
											while (matcher.find()) {
												strArray[j] = "内线电话";
												break;
											}
										}
										/*
										 * if(strArray[j] == null) { Pattern
										 * pEmail =
										 * Pattern.compile("[0-9A-Za-z\\-_\\.]+"
										 * ); Matcher matcher =
										 * pEmail.matcher(str); while
										 * (matcher.find()) { strArray[j] =
										 * "工号"; break; } } //
										 */
										if (strArray[j] == null) {
											strArray[j] = "其它";
										}
									}
								}
							} else {
								String str = getCellContent(cell).substring(0).trim();
								str = str.replaceAll("\r", "");
								str = str.replaceAll("\n", "");
								str = str.replaceAll("\t", "");
								str = str.replaceAll("\\/", "");
								str = str.replaceAll(" ", "");
								str = str.replaceAll(" ", "");

								System.out.println(strArray[j] + ": " + str);
								if (strArray[j] == "姓名") {
									name = str;
								} else if (strArray[j] == "工号") {
									number = str;
								} else if (strArray[j] == "职位") {
									position = str;
								} else if (strArray[j] == "部门") {
									department = str;
								} else if (strArray[j] == "汇报对象") {
									reportPerson = str;
								} else if (strArray[j] == "HR花名") {
									nickeName = str;
								} else if (strArray[j] == "邮箱") {
									email = str;
								} else if (strArray[j] == "内线电话") {
									internalLine = str;
								} else if (strArray[j] == "手机") {
									cellPhone = str;
								} else if (strArray[j] == "团队") {
									team = str;
								} else if (strArray[j] == "其它") {
									if (other != null)
										other += str + ",";
									else
										other = str + ",";
								} else {
									if (other != null)
										other += str + ",";
									else
										other = str + ",";
								}

							} // if(strArray[j] != null)
 						} // if(cell != null)
					} // for()遍历列
					if (i > 0) {
						try { // */
							Connection connect = DriverManager.getConnection("jdbc:mysql://123.57.35.10:3306/hunter2",
									"root", "hunter");
							java.util.Properties prop = new java.util.Properties();
							prop.put(" charSet ", " utf8 ");
							PreparedStatement statement = connect.prepareStatement(
									"INSERT INTO contact_list VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
									Statement.RETURN_GENERATED_KEYS);
							statement.setString(1, null);
							statement.setString(2, name);
							statement.setString(3, number);
							statement.setString(4, position);
							statement.setString(5, department);
							statement.setString(6, reportPerson);
							statement.setString(7, nickeName);
							statement.setString(8, email);
							statement.setString(9, internalLine);
							statement.setString(10, cellPhone);
							statement.setString(11, team);
							statement.setString(12, other);
							statement.setString(13, customerID); // 后期识别
							statement.setString(14, candidateID);// 后期识别
							statement.setInt(15, userID);
							statement.setInt(16, companyID);
							contactList.setId(id);
							contactList.setName(name);
							contactList.setNumber(number);
							contactList.setPosition(position);
							contactList.setDepartment(department);
							contactList.setReportPerson(reportPerson);
							contactList.setNickeName(nickeName);
							contactList.setEmail(email);
							contactList.setInternalLine(internalLine);
							contactList.setCellPhone(cellPhone);
							contactList.setTeam(team);
							contactList.setOther(other);
							contactList.setCustomerId(customerID);
							contactList.setCandidateId(candidateID);
							contactList.setUserId(userID);
							contactList.setCompanyId(companyID);
							dao.insertContact(contactList);
							/*
							 * System.out.println(
							 * "============contact list===========");
							 * System.out.println(contactList.getId());
							 * System.out.println(contactList.getName());
							 * System.out.println(contactList.getNumber());
							 * System.out.println(contactList.getPosition());
							 * System.out.println(contactList.getDepartment());
							 * System.out.println(contactList.getReportPerson())
							 * ; System.out.println(contactList.getNickeName());
							 * System.out.println(contactList.getEmail());
							 * System.out.println(contactList.getInternalLine())
							 * ; System.out.println(contactList.getCellPhone());
							 * System.out.println(contactList.getTeam());
							 * System.out.println(contactList.getOther());
							 * System.out.println(contactList.getUserId());
							 * System.out.println(contactList.getCompanyId());
							 * System.out.println(
							 * "============contact list end==========="); //
							 */
							System.out.println(contactList.getOther());
							// statement.executeUpdate();
							list.add(contactList);
							/// *
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				} // if(row != null)
			} // 遍历行
				// */
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
		// dao.updateCVFileFlag(id);
		return contactList;
	}

	/* 读取Excel文件内容（单个sheet） */
	private static ContactList getCSVValueBehind(List<ContactList> list, String filePath, int sheetIndex,
			String userFileName, int userID, int companyID, int id, IDatabaseDao dao) throws IOException {
		ContactList contactList = new ContactList();
		CsvReader reader = new CsvReader(filePath, ',', Charset.forName("GBK"));// shift_jis日语字体,utf-8
		reader.readHeaders();
		String[] headers = reader.getHeaders();

		List<Object[]> list1 = new ArrayList<Object[]>();
		while (reader.readRecord()) {
			list1.add(reader.getValues());
		}
		Object[][] datas = new String[list1.size()][];
		for (int i = 0; i < list1.size(); i++) {
			datas[i] = list1.get(i);
		}

		/*
		 * 以下输出
		 */
		String[] strArray = new String[2000];
		for (int i = 0; i < headers.length; i++) {
			System.out.print(headers[i] + "\t");
			if (strArray[i] == null) {
				// 解析非合并单元格
				String str = headers[i];
				str = str.trim();
				str = str.replaceAll("\r", "");
				str = str.replaceAll("\n", "");
				str = str.replaceAll("\t", "");
				str = str.replaceAll("\\/", "");
				str = str.replaceAll(" ", "");
				if (str != "") {
					int type = 0;
					if (m_UserDic.containsKey(str)) {// 识别到表头
						type = m_UserDic.get(str);
						switch (type) {
						case 1:
							strArray[i] = "姓名";
							break;
						case 2:
							strArray[i] = "工号";
							break;
						case 3:
							strArray[i] = "职位";
							break;
						case 4:
							strArray[i] = "部门";
							break;
						case 5:
							strArray[i] = "汇报对象";
							break;
						case 6:
							strArray[i] = "HR花名";
							break;
						case 7:
							strArray[i] = "邮箱";
							break;
						case 8:
							strArray[i] = "内线电话";
							break;
						case 9:
							strArray[i] = "手机";
							break;
						case 10:
							strArray[i] = "团队";
							break;
						default:
							strArray[i] = "其它";
							break;
						}// switch
					} // if
				}
			}
		}
		System.out.println("");

		for (int i = 0; i < datas.length; i++) {
			String name = null;
			String number = null;
			String position = null;
			String department = null;
			String reportPerson = null;
			String nickeName = null;
			String email = null;
			String internalLine = null;
			String cellPhone = null;
			String team = null;
			String other = null;
			String customerID = null;
			String candidateID = null;
			int flag = 0;
			Object[] data = datas[i]; // 取出一组数据
			for (int j = 0; j < data.length; j++) {
				Object cell = data[j];
				System.out.print(cell + "\t");
				if (cell != null) {
					if (strArray[j] == null) {
						// 解析非合并单元格
						String str = cell.toString();
						str = str.trim();
						str = str.replaceAll("\r", "");
						str = str.replaceAll("\n", "");
						str = str.replaceAll("\t", "");
						str = str.replaceAll("\\/", "");
						str = str.replaceAll(" ", "");
						if (str != "") {
							int type = 0;
							if (m_UserDic.containsKey(str)) {// 识别到表头
								type = m_UserDic.get(str);
								switch (type) {
								case 1:
									strArray[j] = "姓名";
									break;
								case 2:
									strArray[j] = "工号";
									break;
								case 3:
									strArray[j] = "职位";
									break;
								case 4:
									strArray[j] = "部门";
									break;
								case 5:
									strArray[j] = "汇报对象";
									break;
								case 6:
									strArray[j] = "HR花名";
									break;
								case 7:
									strArray[j] = "邮箱";
									break;
								case 8:
									strArray[j] = "内线电话";
									break;
								case 9:
									strArray[j] = "手机";
									break;
								case 10:
									strArray[j] = "团队";
									break;
								default:
									strArray[j] = "其它";
									break;
								}// switch
							} // if
							else {
								String strOne = detectName(str);
								if (strOne != null && strOne != "") {
									strArray[j] = "姓名";
									name = str;
								}
								if (strArray[j] == null) {
									Pattern pPhone = Pattern.compile("13\\d{9}|15\\d{9}|18\\d{9}|14\\d{9}|17\\d{9}");
									Matcher matcher = pPhone.matcher(str);
									while (matcher.find()) {
										strArray[j] = "手机";
										break;
									}
								}
								if (strArray[j] == null) {
									Pattern pEmail = Pattern
											.compile("([0-9A-Za-z\\-_\\.]+)@([0-9a-z]+\\.[a-z]{2,3}(\\.[a-z]{2})?)");
									Matcher matcher = pEmail.matcher(str);
									while (matcher.find()) {
										strArray[j] = "邮箱";
										break;
									}
								}
								if (strArray[j] == null) {
									Pattern pEmail = Pattern.compile(
											"\\d{3,4}-\\d{4}-\\d{4}|\\d{3,4}-\\d{6,8}|\\d{3,4}-\\d{4}|\\d{7,8}|\\d{11,12}");
									Matcher matcher = pEmail.matcher(str);
									while (matcher.find()) {
										strArray[j] = "内线电话";
										break;
									}
								}
								/*
								 * if(strArray[j] == null) { Pattern pEmail =
								 * Pattern.compile("[0-9A-Za-z\\-_\\.]+");
								 * Matcher matcher = pEmail.matcher(str); while
								 * (matcher.find()) { strArray[j] = "工号"; break;
								 * } } //
								 */
								if (strArray[j] == null) {
									strArray[j] = "其它";
								}
							}
						}
					} else {
						flag = 1;
						String str = cell.toString();
						str = str.trim();
						str = str.replaceAll("\r", "");
						str = str.replaceAll("\n", "");
						str = str.replaceAll("\t", "");
						str = str.replaceAll("\\/", "");
						str = str.replaceAll(" ", "");
						str = str.replaceAll(" ", "");

						System.out.println(strArray[j] + ": " + str);
						if (strArray[j] == "姓名") {
							name = str;
						} else if (strArray[j] == "工号") {
							number = str;
						} else if (strArray[j] == "职位") {
							position = str;
						} else if (strArray[j] == "部门") {
							department = str;
						} else if (strArray[j] == "汇报对象") {
							reportPerson = str;
						} else if (strArray[j] == "HR花名") {
							nickeName = str;
						} else if (strArray[j] == "邮箱") {
							email = str;
						} else if (strArray[j] == "内线电话") {
							internalLine = str;
						} else if (strArray[j] == "手机") {
							cellPhone = str;
						} else if (strArray[j] == "团队") {
							team = str;
						} else if (strArray[j] == "其它") {
							if (other != null)
								other += str + ",";
							else
								other = str + ",";
						} else {
							if (other != null)
								other += str + ",";
							else
								other = str + ",";
						}

					} // if(strArray[j] != null)
				}
			}
			System.out.println("");
			if (flag == 1) {
				// Connection connect = DriverManager.getConnection(
				// "jdbc:mysql://localhost:3306/hunter2","root","");
				// java.util.Properties prop = new java.util.Properties();
				// prop.put( " charSet " , " utf8 " );
				// PreparedStatement statement=connect.prepareStatement("INSERT
				// INTO contact_list
				// VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",PreparedStatement.RETURN_GENERATED_KEYS);
				// statement.setString(1,null);
				// statement.setString(2,name);
				// statement.setString(3,null);
				// statement.setString(4,number);
				// statement.setString(5,position);
				// statement.setString(6,department);
				// statement.setString(7,reportPerson);
				// statement.setString(8,nickeName);
				// statement.setString(9,email);
				// statement.setString(10,internalLine);
				// statement.setString(11,cellPhone);
				// statement.setString(12,team);
				// statement.setString(13,other);
				// statement.setString(14,customerID); //后期识别
				// statement.setString(15,candidateID);//后期识别
				// statement.setInt(16,userID);
				// statement.setInt(17,companyID);
				// statement.setString(18,null);
				contactList.setId(id);
				contactList.setName(name);
				contactList.setNumber(number);
				contactList.setPosition(position);
				contactList.setDepartment(department);
				contactList.setReportPerson(reportPerson);
				contactList.setNickeName(nickeName);
				contactList.setEmail(email);
				contactList.setInternalLine(internalLine);
				contactList.setCellPhone(cellPhone);
				contactList.setTeam(team);
				contactList.setOther(other);
				contactList.setCustomerId(customerID);
				contactList.setCandidateId(candidateID);
				contactList.setUserId(userID);
				contactList.setCompanyId(companyID);
				dao.insertContact(contactList);
				System.out.println("============contact list===========");
				System.out.println(contactList.getId());
				System.out.println(contactList.getName());
				System.out.println(contactList.getNumber());
				System.out.println(contactList.getPosition());
				System.out.println(contactList.getDepartment());
				System.out.println(contactList.getReportPerson());
				System.out.println(contactList.getNickeName());
				System.out.println(contactList.getEmail());
				System.out.println(contactList.getInternalLine());
				System.out.println(contactList.getCellPhone());
				System.out.println(contactList.getTeam());
				System.out.println(contactList.getOther());
				System.out.println(contactList.getUserId());
				System.out.println(contactList.getCompanyId());
				System.out.println("============contact list end===========");
				// statement.executeUpdate();
				/// *
			}
		}
		// return null;
		return contactList;
	}

	/* 读取Excel文件内容（单个sheet） */
	private static ContactList getExcelValue(List<ContactList> list, String filePath, int sheetIndex,
			String userFileName, Workbook book, int userID, int companyID, int id) {
		int currentid = 0;
		String value = "";
		ContactList contactList = new ContactList();
		try {
			Sheet sheet = book.getSheetAt(sheetIndex);
			// 获取到Excel文件中的所有行数
			int rows = sheet.getLastRowNum();
			// 遍历行
			String[] strArray = new String[2000];
			int numberofdatastructure = 1;
			for (int i = 0; i <= rows; i++) {
				int compactcolomn = 0;
				// 读取左上端单元格
				Row row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					// 获取到Excel文件中的所有的列
					int cells = row.getLastCellNum();
					String name = null;
					String number = null;
					String position = null;
					String department = null;
					String reportPerson = null;
					String nickeName = null;
					String email = null;
					String internalLine = null;
					String cellPhone = null;
					String team = null;
					String other = null;
					String customerID = null;
					String candidateID = null;
					// 遍历列
					for (int j = 0; j <= cells; j++) {
						// 获取到列的值
						Cell cell = row.getCell(j);
						if (cell != null) {
							if (strArray[j] == null) {
								// 解析非合并单元格
								String str = getCellContent(cell).substring(0).trim();
								str = str.replaceAll("\r", "");
								str = str.replaceAll("\n", "");
								str = str.replaceAll("\t", "");
								str = str.replaceAll("\\/", "");
								str = str.replaceAll(" ", "");
								if (str != "") {
									int type = 0;
									if (m_UserDic.containsKey(str)) {// 识别到表头
										type = m_UserDic.get(str);
										switch (type) {
										case 1:
											strArray[j] = "姓名";
											break;
										case 2:
											strArray[j] = "工号";
											break;
										case 3:
											strArray[j] = "职位";
											break;
										case 4:
											strArray[j] = "部门";
											break;
										case 5:
											strArray[j] = "汇报对象";
											break;
										case 6:
											strArray[j] = "HR花名";
											break;
										case 7:
											strArray[j] = "邮箱";
											break;
										case 8:
											strArray[j] = "内线电话";
											break;
										case 9:
											strArray[j] = "手机";
											break;
										case 10:
											strArray[j] = "团队";
											break;
										default:
											strArray[j] = "其它";
											break;
										}// switch
									} // if
									else {
										String strOne = detectName(str);
										if (strOne != null && strOne != "") {
											strArray[j] = "姓名";
											name = str;
										}
										if (strArray[j] == null) {
											Pattern pPhone = Pattern
													.compile("13\\d{9}|15\\d{9}|18\\d{9}|14\\d{9}|17\\d{9}");
											Matcher matcher = pPhone.matcher(str);
											while (matcher.find()) {
												strArray[j] = "手机";
												break;
											}
										}
										if (strArray[j] == null) {
											Pattern pEmail = Pattern.compile(
													"([0-9A-Za-z\\-_\\.]+)@([0-9a-z]+\\.[a-z]{2,3}(\\.[a-z]{2})?)");
											Matcher matcher = pEmail.matcher(str);
											while (matcher.find()) {
												strArray[j] = "邮箱";
												break;
											}
										}
										if (strArray[j] == null) {
											Pattern pEmail = Pattern.compile(
													"\\d{3,4}-\\d{4}-\\d{4}|\\d{3,4}-\\d{6,8}|\\d{3,4}-\\d{4}|\\d{7,8}|\\d{11,12}");
											Matcher matcher = pEmail.matcher(str);
											while (matcher.find()) {
												strArray[j] = "内线电话";
												break;
											}
										}
										/*
										 * if(strArray[j] == null) { Pattern
										 * pEmail =
										 * Pattern.compile("[0-9A-Za-z\\-_\\.]+"
										 * ); Matcher matcher =
										 * pEmail.matcher(str); while
										 * (matcher.find()) { strArray[j] =
										 * "工号"; break; } } //
										 */
										if (strArray[j] == null) {
											strArray[j] = "其它";
										}
									}
								}
							} else {
								String str = getCellContent(cell).substring(0).trim();
								str = str.replaceAll("\r", "");
								str = str.replaceAll("\n", "");
								str = str.replaceAll("\t", "");
								str = str.replaceAll("\\/", "");
								str = str.replaceAll(" ", "");
								str = str.replaceAll(" ", "");

								System.out.println(strArray[j] + ": " + str);
								if (strArray[j] == "姓名") {
									name = str;
								} else if (strArray[j] == "工号") {
									number = str;
								} else if (strArray[j] == "职位") {
									position = str;
								} else if (strArray[j] == "部门") {
									department = str;
								} else if (strArray[j] == "汇报对象") {
									reportPerson = str;
								} else if (strArray[j] == "HR花名") {
									nickeName = str;
								} else if (strArray[j] == "邮箱") {
									email = str;
								} else if (strArray[j] == "内线电话") {
									internalLine = str;
								} else if (strArray[j] == "手机") {
									cellPhone = str;
								} else if (strArray[j] == "团队") {
									team = str;
								} else if (strArray[j] == "其它") {
									if (other != null)
										other += str + ",";
									else
										other = str + ",";
								} else {
									if (other != null)
										other += str + ",";
									else
										other = str + ",";
								}

							} // if(strArray[j] != null)
						} // if(cell != null)
					} // for()遍历列
					if (i > 0) {
						try { // */
							Connection connect = DriverManager.getConnection("jdbc:mysql://123.57.35.10:3306/hunter2",
									"root", "hunter");
							java.util.Properties prop = new java.util.Properties();
							prop.put(" charSet ", " utf8 ");
							PreparedStatement statement = connect.prepareStatement(
									"INSERT INTO contact_list VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
									Statement.RETURN_GENERATED_KEYS);
							statement.setString(1, null);
							statement.setString(2, name);
							statement.setString(3, number);
							statement.setString(4, position);
							statement.setString(5, department);
							statement.setString(6, reportPerson);
							statement.setString(7, nickeName);
							statement.setString(8, email);
							statement.setString(9, internalLine);
							statement.setString(10, cellPhone);
							statement.setString(11, team);
							statement.setString(12, other);
							statement.setString(13, customerID); // 后期识别
							statement.setString(14, candidateID);// 后期识别
							statement.setInt(15, userID);
							statement.setInt(16, companyID);
							contactList.setId(id);
							contactList.setName(name);
							contactList.setNumber(number);
							contactList.setPosition(position);
							contactList.setDepartment(department);
							contactList.setReportPerson(reportPerson);
							contactList.setNickeName(nickeName);
							contactList.setEmail(email);
							contactList.setInternalLine(internalLine);
							contactList.setCellPhone(cellPhone);
							contactList.setTeam(team);
							contactList.setOther(other);
							contactList.setCustomerId(customerID);
							contactList.setCandidateId(candidateID);
							contactList.setUserId(userID);
							contactList.setCompanyId(companyID);
							// dao.insertContact(contactList);
							// statement.executeUpdate();
							list.add(contactList);
							/// *
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				} // if(row != null)
			} // 遍历行
				// */
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
		return contactList;
	}

	/// *
	public static String importList(int userId, int companyId, String filePath) {

		// String userID = userId;//传入参数
		// String companyId = companyId;//传入参数
		int id = 0;
		IDatabaseDao dao;

		// String filePath = "C:\\Users\\Shu\\Desktop\\list.xlsx";
		int sheetIndex = 0;// 需要获取sheet数量，并分别处理
		// 创建对Excel工作簿文件
		/// *
		Workbook book = null;
		List<ContactList> list = new ArrayList<ContactList>();
		if (filePath.endsWith("xls")) {
			try {
				book = new HSSFWorkbook(new FileInputStream(filePath));
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} else {
			try {
				book = new XSSFWorkbook(new FileInputStream(filePath));
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		// */
		String userFileName = "";
		Pattern fileName = Pattern.compile("([0-9A-Za-z\\-_\\.]+).xls|([0-9A-Za-z\\-_\\.]+).xlsx");
		Matcher matcher = fileName.matcher(filePath);
		while (matcher.find()) {
			System.out.println(matcher.group());
			userFileName += matcher.group();
			break;
		}
		int numberOfSheet = book.getNumberOfSheets();
		for (sheetIndex = 0; sheetIndex < numberOfSheet; sheetIndex++) {
			getExcelValue(list, filePath, sheetIndex, userFileName, book, userId, companyId, id);
		}

		return JSONUtil.ArrayToJSON(list);
	}
	// */

	// 导入联系人
	public static void parseBehind(int id, int userId, int companyId, String filePath, IDatabaseDao dao) {

		// String userID = userId;//传入参数
		// String companyId = companyId;//传入参数

		// String filePath = "C:\\Users\\Shu\\Desktop\\list.xlsx";
		int sheetIndex = 0;// 需要获取sheet数量，并分别处理
		// 创建对Excel工作簿文件
		/// *
		Workbook book = null;
		List<ContactList> list = new ArrayList<ContactList>();
		if (filePath.endsWith("xls")) {
			try {
				book = new HSSFWorkbook(new FileInputStream(filePath));
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} else {
			try {
				book = new XSSFWorkbook(new FileInputStream(filePath));
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		// */
		String userFileName = "";
		Pattern fileName = Pattern.compile("([0-9A-Za-z\\-_\\.]+).xls|([0-9A-Za-z\\-_\\.]+).xlsx");
		Matcher matcher = fileName.matcher(filePath);
		while (matcher.find()) {
			// System.out.println(matcher.group());
			userFileName += matcher.group();
			break;
		}
		if (book != null) {
			dao.updateCVFileFlag(id);
			int numberOfSheet = book.getNumberOfSheets();
			for (sheetIndex = 0; sheetIndex < numberOfSheet; sheetIndex++) {
				getExcelValueBehind(list, filePath, sheetIndex, userFileName, book, userId, companyId, id, dao);
			}
			dao.updateCVFileFlag(id);
		}
	}

	// 导入联系人
	public static void parseCSVBehind(int id, int userId, int companyId, String filePath, IDatabaseDao dao)
			throws IOException {

		String userID = null;// 传入参数
		String companyID = null;// 传入参数
		List<ContactList> list = new ArrayList<ContactList>();

		// String filePath = "C:\\Users\\Shu\\Desktop\\高舒_试用bug列表.csv";
		int sheetIndex = 0;// 需要获取sheet数量，并分别处理
		String userFileName = "";
		Pattern fileName = Pattern.compile("([0-9A-Za-z\\-_\\.]+).csv");
		Matcher matcher = fileName.matcher(filePath);
		while (matcher.find()) {
			System.out.println(matcher.group());
			userFileName += matcher.group();
			break;
		}
		dao.updateCVFileFlag(id);
		getCSVValueBehind(list, filePath, sheetIndex, userFileName, userId, companyId, id, dao);
		dao.updateCVFileFlag(id);
	}

}
